﻿using ExcelSystem.Model;
using ExcelSystem.ToolClass;
using ExcelSystem.ViewModelsClass;
using ImTools;
using Newtonsoft.Json;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;

using System.Diagnostics;
using System.Security.Policy;
using ExcelSystem.Event;
using Prism.Events;
using NPOI.SS.Formula;
using Microsoft.IdentityModel.Tokens;
using ExcelSystem.Views;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using static ImTools.ImMap;
using Prism.Services.Dialogs;
using NPOI.XSSF.Streaming.Values;
using System.Collections.ObjectModel;
using ControlzEx.Standard;









namespace ExcelSystem.ToolHelper
{
    /// <summary>
    /// EXCEL表格导入数据
    /// </summary>
    public static class ExcelUserHelper
    {

        static ExcelContext db = ExcelContentFactory.GetInstance();

        static List<Model.Formula> formulas;

        public static void Init()
        {
            formulas = db.Formulas.ToList();
        }

     


        /// <summary>
        /// 打开文件选择页面
        /// </summary>
        /// <returns></returns>
        public static string OpenExcelSystemDialog()
        {
            var ofd = new Microsoft.Win32.OpenFileDialog()
            {
                DefaultExt = "xls",
                Filter = "excel files|*.xls;*.xlsm;*.xlsx;",
                FilterIndex = 1,
                Multiselect = true,
            };


            if (ofd.ShowDialog() != true)
                return null;
            return ofd.FileName;
        }
        public static List<string> OpenExcelSystemDialogs()
        {
            var ofd = new Microsoft.Win32.OpenFileDialog()
            {
                DefaultExt = "xls",
                Filter = "excel files|*.xls;*.xlsm;*.xlsx;",
                FilterIndex = 1,
                Multiselect = true,
            };


            if (ofd.ShowDialog() != true)
                return null;
            return ofd.FileNames.ToList();
        }

        public static void ImportExcelFile(MaterialViewModel obj)
        {

            //string a = null;
            //var b=a.ToString();
            //PrintingXiAo(obj);
            //ReadExcelXiAo(OpenExcelFileDialog());
            //readExcel(OpenExcelFileDialog(), typeof(Order2), 1);
            //ReadNS(OpenExcelFileDialog());
            //ReadBOM1(OpenExcelSystemDialog());
            //ReadBOM2(OpenExcelSystemDialog());
            ReadBOM3(OpenExcelSystemDialog());
            //ReadBOM5(OpenExcelSystemDialog());
            ReadBOM4(OpenExcelSystemDialog());
            //货梯
            ReadBOM6(OpenExcelSystemDialog());





        }

        public static string ImportExcelSystem()
        {
            string result = "";
            result = ReadExcelXiAo(OpenExcelSystemDialog());
            return result;
        }
        public static string ReadExcelXiAo(string fileName)
        {
            string result = "";
            try
            {
                using (FileStream fs = File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook workBook = null;
                    // 根据版本号创建不同版本的excel


                    workBook = WorkbookFactory.Create(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(0);
                    List<Order> OrderList = new List<Order>();
                    var dataTabale = new System.Data.DataTable();
                    //获取筛选信息
                    FilterValue filters = new FilterValue();
                    var Filter = db.Filters.FirstOrDefault(t => t.Company == "西奥" && t.Name.Contains("MaterialNo"));
                    if (Filter != null)
                    {
                        filters = JsonConvert.DeserializeObject<FilterValue>(Filter.Value);
                    }

                    //获取订单号
                    for (int i = 0; i < sheet.LastRowNum; i++)
                    {
                        //遍历所有第一行获取订单号
                        var order_k = sheet.GetRow(i).GetCell(0).ToString();
                        var order_v = sheet.GetRow(i).GetCell(1).ToString();
                        if (order_k != null && order_k.Contains("订单号"))
                        {
                            if(order_v== "4200486829")
                            {

                            }

                            for (int j = i; j < sheet.LastRowNum; j++)
                            {
                                var line_k = sheet.GetRow(j).GetCell(0).ToString();


                                if (line_k != null && line_k.Contains("订单行"))
                                {

                                    var order = new Order() { OrderNo = order_v, CreateTime = DateTime.Now, UpdateTime = DateTime.Now };
                                    int n = j;



                                    //1
                                    order.OrderLine = sheet.GetRow(n).GetCell(1).ToString();
                                    order.Factory = sheet.GetRow(n).GetCell(3).ToString();
                                    order.ProcurementTypePar = sheet.GetRow(n).GetCell(5).ToString();
                                    order.SchedulingNo = sheet.GetRow(n).GetCell(7).ToString();
                                    order.ElevatorType = sheet.GetRow(n).GetCell(9).ToString();
                                    //2
                                    n = j + 1;
                                    order.ConstructionSiteModule = sheet.GetRow(n).GetCell(1).ToString();
                                    order.BoxName = sheet.GetRow(n).GetCell(3).ToString();
                                    order.ContractNo = sheet.GetRow(n).GetCell(5).ToString();

                                    //唯一性判断
                                    int count = db.Orders.Count(t => t.ContractNo == order.ContractNo);

                                    order.SchedulingDate = Convert.ToDateTime(sheet.GetRow(n).GetCell(7).ToString());
                                    order.NamePlate = Convert.ToDateTime(sheet.GetRow(n).GetCell(9).ToString());
                                    //有较少的 
                                    if (order.ConstructionSiteModule == "曳引机底座工地模块")
                                    {
                                        //3
                                        n = j + 2;
                                        order.PackagingBoxStatus = sheet.GetRow(n).GetCell(1).ToString();
                                        order.OutsourcingSize = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.BoxPrinting = sheet.GetRow(n).GetCell(5).ToString();
                                        order.CustomType = sheet.GetRow(n).GetCell(5).ToString();

                                        //4
                                        n = j + 3;
                                        order.Color = sheet.GetRow(n).GetCell(1).ToString();
                                        order.Spped = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.ExportElevator = sheet.GetRow(n).GetCell(5).ToString() == "是" ? true : false;

                                        n = j + 6;
                                        order.MaterialNo = sheet.GetRow(n).GetCell(0).ToString();
                                        order.MaterialDescribe = sheet.GetRow(n).GetCell(1).ToString();
                                        order.Number = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(2).ToString());
                                        order.Unit = sheet.GetRow(n).GetCell(3).ToString();
                                        order.DeliveryDate = Convert.ToDateTime(sheet.GetRow(n).GetCell(4).ToString());
                                        order.MaterialSpecifications = sheet.GetRow(n).GetCell(5).ToString();
                                        order.BOMSpecifications = sheet.GetRow(n).GetCell(6).ToString();
                                        order.TTBatch = sheet.GetRow(n).GetCell(7).ToString();
                                        order.SequenceNumber = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(8).ToString());
                                        order.Remarks = sheet.GetRow(n).GetCell(9).ToString();

                                        //14 15 跳过
                                        n = j + 9;

                                        List<Material> materialList = new List<Material>();
                                        //下层物料信息
                                        for (int k = 0; k < 100; k++)
                                        {

                                            Material material = new Material();
                                            material.OrderNo = order.OrderNo;
                                            material.OrderLine = order.OrderLine;
                                            material.ContractNo = order.ContractNo;
                                            var row = sheet.GetRow(n + k);
                                            if (row == null)
                                                continue;

                                            material.BOMLevel = sheet.GetRow(n + k).GetCell(0).ToString();
                                            material.MaterialNo = sheet.GetRow(n + k).GetCell(1).ToString();
                                            if (filters!=null&& filters.AccurateName!=null&& filters.AccurateName.Contains(material.MaterialNo))
                                                continue;
                                            if(filters!=null&& filters.VagueName!=null)
                                            {
                                                var vague = filters.VagueName.FirstOrDefault(t => material.MaterialNo.Contains(t));
                                                if (vague != null)
                                                    continue;
                                            }
                                      
                                            material.MaterialDescribe = sheet.GetRow(n + k).GetCell(2).ToString();
                                            material.Unit = sheet.GetRow(n + k).GetCell(4).ToString();
                                            material.MaterialSpecifications = sheet.GetRow(n + k).GetCell(6).ToString();
                                            if (!string.IsNullOrEmpty(material.MaterialSpecifications))
                                            {
                                                //对物料进行筛选
                                                var list = material.MaterialSpecifications.Split(';');
                                                //获取颜色
                                                material.Color = string.IsNullOrEmpty(list.FirstOrDefault(t => t.Contains("RAL"))) ? "" : list.FirstOrDefault(t => t.Contains("RAL")).Replace("=", "");
                                                //获取L
                                                var L = list.FirstOrDefault(t => !t.Contains("RAL") && (t.Contains("Ｌ") || t.Contains("L")));
                                                //获取H(特殊字符，存在H时计算数量时用H当分母)
                                                var H = list.FirstOrDefault(t => !t.Contains("RAL") && (t.Contains("Ｈ") || t.Contains("H")));

                                                if (string.IsNullOrEmpty(L))
                                                {
                                                    material.L = "";
                                                    if (material.Unit == "M")
                                                    {
                                                        material.L = (ConvetHelper.ToDouble(sheet.GetRow(n + k).GetCell(3).ToString()) * 1000).ToString();
                                                    }
                                                }
                                                else
                                                {
                                                    L = L.Substring(L.IndexOf("=") + 1, L.Length - L.IndexOf("=") - 1);
                                                    //数据来源 3.8 3.8m  3.8M  3800mm  3800   统一到3800   单位为M 且没L的情况  L=数量*1000； EA=数量就是实际数量
                                                    if (L.Where(t => t == 'm').Count() == 1 || L.Where(t => t == 'M').Count() == 1 || L.Where(t => t == '.').Count() == 1)
                                                    {
                                                        material.L = (double.Parse(L.Replace("M", "").Replace("m", "")) * 1000).ToString();
                                                    }
                                                    else
                                                    {
                                                        material.L = L.Replace("M", "").Replace("m", "");
                                                    }

                                                }
                                                //material.L = string.IsNullOrEmpty(list.FirstOrDefault(t => !t.Contains("RAL") && t.Contains("Ｌ"))) ?
                                                //    "" : list.FirstOrDefault(t => !t.Contains("RAL") && t.Contains("Ｌ")).Split("=")[1].Replace("m", "").Replace("M", "");

                                                if (!string.IsNullOrEmpty(H))
                                                {
                                                    H = H.Substring(H.IndexOf("=") + 1, H.Length - H.IndexOf("=") - 1);
                                                    if (material.Unit == "M")
                                                    {
                                                        material.L = (Convert.ToDecimal(H) * 1000).ToString();
                                                    }

                                                    material.Number = ConvetHelper.ToDecimal(sheet.GetRow(n + k).GetCell(3).ToString()) / Convert.ToDecimal(H);
                                                }

                                                else if (!string.IsNullOrEmpty(material.L))
                                                {
                                                    material.Number = ConvetHelper.ToDecimal(sheet.GetRow(n + k).GetCell(3).ToString()) * 1000 / Convert.ToDecimal(material.L);

                                                }
                                                else
                                                {
                                                    material.Number = ConvetHelper.ToDecimal(sheet.GetRow(n + k).GetCell(3).ToString());
                                                }
                                            }
                                            material.ProcurementTypePar = sheet.GetRow(n + k).GetCell(5).ToString();
                                            material.BOMSpecifications = sheet.GetRow(n + k).GetCell(7).ToString();
                                            material.Remarks = sheet.GetRow(n + k).GetCell(8).ToString();
                                            //有颜色和参数的才保存
                                            if (!string.IsNullOrEmpty(material.MaterialSpecifications))
                                                materialList.Add(material);

                                            //检查到下一个订单号开始，跳出本次循环
                                            var next_m = sheet.GetRow(n + k + 1);
                                            if (next_m != null && (next_m.GetCell(0).ToString().Contains("订单号") || next_m.GetCell(0).ToString().Contains("订单行")))
                                            {
                                                n = n + k;
                                                break;
                                            }
                                        }
                                        db.Materials.AddRange(materialList);
                                        db.SaveChanges();
                                        OrderList.Add(order);

                                        //检查到下一个订单号开始，跳出本次循环
                                        var next = sheet.GetRow(n + 1);
                                        if (next != null && next.GetCell(0).ToString().Contains("订单号"))
                                        {
                                            break;
                                        }
                                        j = n;


                                    }
                                    else
                                    {

                                        //3
                                        n = j + 2;
                                        order.PackagingBoxStatus = sheet.GetRow(n).GetCell(1).ToString();
                                        order.ShaftWidth = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.ShaftDepth = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(5).ToString());
                                        order.distance = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(7).ToString());
                                        //4
                                        n = j + 3;
                                        order.distance4 = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(1).ToString());
                                        order.CarRoofWheelArrange = sheet.GetRow(n).GetCell(3).ToString();
                                        order.OutsourcingSize = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(5).ToString());
                                        order.distance2 = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(7).ToString());
                                        //5
                                        n = j + 4;
                                        order.ShaftWallThickness = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(1).ToString());
                                        order.HallDoorSpace = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.BoxPrinting = sheet.GetRow(n).GetCell(5).ToString();
                                        order.distance3 = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(7).ToString());
                                        //6
                                        n = j + 5;
                                        order.CounterweightArrange = sheet.GetRow(n).GetCell(1).ToString();
                                        order.HostSeries = sheet.GetRow(n).GetCell(3).ToString();
                                        order.BearingBeamAngle = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(5).ToString());
                                        order.CounterweightWheelDameter = sheet.GetRow(n).GetCell(7).ToString();
                                        //7
                                        n = j + 6;
                                        order.OuterDepth = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(1).ToString());
                                        order.distance5 = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.distance6 = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(5).ToString());
                                        order.CounterWeightShaftDepth = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(7).ToString());
                                        //8
                                        n = j + 7;
                                        order.PulleyDiameter = sheet.GetRow(n).GetCell(1).ToString();
                                        order.CarOuterWidth = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.Load = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(7).ToString());
                                        //9
                                        n = j + 8;
                                        order.Spped = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(1).ToString());
                                        order.DoorsOpenedNo = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(3).ToString());
                                        order.StopsNo = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(5).ToString());
                                        order.distance7 = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(7).ToString());
                                        //10
                                        n = j + 9;
                                        order.SpeedLimiterPosition = sheet.GetRow(n).GetCell(1).ToString();
                                        order.SteelWireRopesNo = sheet.GetRow(n).GetCell(3).ToString();
                                        order.ExportElevator = sheet.GetRow(n).GetCell(7).ToString() == "是" ? true : false;
                                        //11  12 标题 跳过
                                        //13
                                        n = j + 12;
                                        order.MaterialNo = sheet.GetRow(n).GetCell(0).ToString();
                                        order.MaterialDescribe = sheet.GetRow(n).GetCell(1).ToString();
                                        order.Number = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(2).ToString());
                                        order.Unit = sheet.GetRow(n).GetCell(3).ToString();
                                        order.DeliveryDate = Convert.ToDateTime(sheet.GetRow(n).GetCell(4).ToString());
                                        order.MaterialSpecifications = sheet.GetRow(n).GetCell(5).ToString();
                                        order.BOMSpecifications = sheet.GetRow(n).GetCell(6).ToString();
                                        order.TTBatch = sheet.GetRow(n).GetCell(7).ToString();
                                        order.SequenceNumber = ConvetHelper.ToDouble(sheet.GetRow(n).GetCell(8).ToString());
                                        order.Remarks = sheet.GetRow(n).GetCell(9).ToString();
                                        //14 15 跳过
                                        n = j + 15;

                                        List<Material> materialList = new List<Material>();
                                        //下层物料信息
                                        for (int k = 0; k < 100; k++)
                                        {
                                            Material material = new Material() { CreateTime = DateTime.Now, UpdateTime = DateTime.Now };
                                            material.OrderNo = order.OrderNo;
                                            material.OrderLine = order.OrderLine;
                                            material.ContractNo = order.ContractNo;
                                            var row = sheet.GetRow(n + k);
                                            if (row == null)
                                                break;
                                            material.BOMLevel = sheet.GetRow(n + k).GetCell(0).ToString();
                                            material.MaterialNo = sheet.GetRow(n + k).GetCell(1).ToString();
                                            if (material.MaterialNo== "XOA4134ABZ954")
                                            {

                                            }

                                            if (filters != null && filters.AccurateName != null && filters.AccurateName.Contains(material.MaterialNo))
                                            {
                                               
                                                //检查到下一个订单号开始，跳出本次循环
                                                var next_f = sheet.GetRow(n + k + 1);
                                                if (next_f != null && (next_f.GetCell(0).ToString().Contains("订单号") || next_f.GetCell(0).ToString().Contains("订单行")))
                                                {
                                                    n = n + k;
                                                    break;
                                                }
                                                else
                                                {
                                                    continue;
                                                }
                                            } 
                                            if(filters.VagueName!=null&& filters.VagueName.Count>0)
                                            {
                                                var vague = filters.VagueName.FirstOrDefault(t => material.MaterialNo.Contains(t));
                                                //检查到下一个订单号开始，跳出本次循环
                                                var next_f = sheet.GetRow(n + k + 1);
                                                if (next_f != null && (next_f.GetCell(0).ToString().Contains("订单号") || next_f.GetCell(0).ToString().Contains("订单行")))
                                                {
                                                    n = n + k;
                                                    break;
                                                }
                                                else if (vague != null)
                                                {
                                                    continue;
                                                }
                                               
                                                   
                                            }
                                          
                                            material.MaterialDescribe = sheet.GetRow(n + k).GetCell(2).ToString();
                                            material.MaterialSpecifications = sheet.GetRow(n + k).GetCell(6).ToString();
                                            material.Unit = sheet.GetRow(n + k).GetCell(4).ToString();
                                            if (!string.IsNullOrEmpty(material.MaterialSpecifications))
                                            {
                                                var list = material.MaterialSpecifications.Split(';');
                                                //获取颜色
                                                material.Color = string.IsNullOrEmpty(list.FirstOrDefault(t => t.Contains("RAL"))) ? "" : list.FirstOrDefault(t => t.Contains("RAL")).Replace("=", "");
                                                //获取L
                                                var L = list.FirstOrDefault(t => !t.Contains("RAL") && (t.Contains("Ｌ") || t.Contains("L")));
                                                //获取H(特殊字符，存在H时计算数量时用H当分母)
                                                var H = list.FirstOrDefault(t => !t.Contains("RAL") && (t.Contains("Ｈ") || t.Contains("H")));
                                                if (string.IsNullOrEmpty(L))
                                                {
                                                    material.L = "";
                                                    if (material.Unit == "M")
                                                    {
                                                        material.L = (ConvetHelper.ToDouble(sheet.GetRow(n + k).GetCell(3).ToString()) * 1000).ToString();
                                                    }

                                                }
                                                else
                                                {
                                                    //截取=后面的数字
                                                    L = L.Substring(L.IndexOf("=") + 1, L.Length - L.IndexOf("=") - 1);
                                                    //数据来源 3.8m  3.8M  3800mm  3800  统一到3800

                                                    if (L.Where(t => t == 'm').Count() == 1 || L.Where(t => t == 'M').Count() == 1 || L.Where(t => t == '.').Count() == 1)
                                                    {
                                                        material.L = (double.Parse(L.Replace("M", "").Replace("m", "")) * 1000).ToString();
                                                    }
                                                    else
                                                    {
                                                        material.L = L.Replace("M", "").Replace("m", "");
                                                    }

                                                }
                                                if (!string.IsNullOrEmpty(H))
                                                {
                                                    H = H.Substring(H.IndexOf("=") + 1, H.Length - H.IndexOf("=") - 1);
                                                    if (material.Unit == "M")
                                                    {
                                                        material.L = (Convert.ToDecimal(H) * 1000).ToString();
                                                    }
                                                
                                                    material.Number = ConvetHelper.ToDecimal(sheet.GetRow(n + k).GetCell(3).ToString()) / Convert.ToDecimal(H);
                                                }

                                                else if (!string.IsNullOrEmpty(material.L))
                                                {
                                                    material.Number = ConvetHelper.ToDecimal(sheet.GetRow(n + k).GetCell(3).ToString()) * 1000 / Convert.ToDecimal(material.L);

                                                }
                                                else
                                                {
                                                    var number = sheet.GetRow(n + k).GetCell(3).ToString();
                                                    material.Number = ConvetHelper.ToDecimal(sheet.GetRow(n + k).GetCell(3).ToString());
                                                }
                                            }



                                            material.ProcurementTypePar = sheet.GetRow(n + k).GetCell(5).ToString();



                                            material.BOMSpecifications = sheet.GetRow(n + k).GetCell(7).ToString();
                                            material.Remarks = sheet.GetRow(n + k).GetCell(8).ToString();
                                            //有颜色和参数的才保存
                                            if (!string.IsNullOrEmpty(material.MaterialSpecifications))
                                                materialList.Add(material);

                                            //检查到下一个订单号开始，跳出本次循环
                                            var next_m = sheet.GetRow(n + k + 1);
                                            if (next_m != null && (next_m.GetCell(0).ToString().Contains("订单号") || next_m.GetCell(0).ToString().Contains("订单行")))
                                            {
                                                n = n + k;
                                                break;
                                            }
                                        }
                                        //唯一键没重复时才能添加
                                        if(count==0)
                                        {
                                            db.Materials.AddRange(materialList);
                                            db.SaveChanges();
                                            OrderList.Add(order);
                                        }
                                    

                                        //检查到下一个订单号开始，跳出本次循环
                                        var next = sheet.GetRow(n + 1);
                                        if (next != null && next.GetCell(0).ToString().Contains("订单号"))
                                        {
                                            break;
                                        }
                                        j = n;
                                    }
                                }
                            }
                        }

                    }
                    db.Orders.AddRange(OrderList);
                    db.SaveChanges();
                    result = $"成功导入{OrderList.Count}条合同数据";
                }
            }
            catch (Exception e)
            {

                result = $" 导出失败，错误:{e.Message}";
            }

            return result;
        }

        /// <summary>
        /// 西奥二行打印
        /// </summary>
        public static void PrintingXiAo(List<MaterialViewModel> material, IDialogService dialogService)
        {
            ExcelInfo excelInfo = new ExcelInfo();
            try
            {

                //获取主梁顶置
               var filters= db.Filters.FirstOrDefault(t => t.Company == "西奥" && t.Name.Contains("MaterialNo"));
                if(filters!=null)
                {
                   var f_v= JsonConvert.DeserializeObject<FilterValue>(filters.Value);
                    if(f_v.OverHead!=null)
                    {
                        List<MaterialViewModel> a = new List<MaterialViewModel>();
                        List<MaterialViewModel> b=new List<MaterialViewModel>();
                        material.ForEach(t =>
                        {
                            var head= f_v.OverHead.FindFirst(j => t.Material.MaterialNo.Contains(j));
                            if(head!=null)
                            {
                                a.Add(t);
                            }
                            else
                            {
                                b.Add(t);
                            }


                        }

                        );
                        a.AddRange(b);
                        material = a;
                    }
                


                }
                else
                {
                    material = material.OrderByDescending(t => t.Material.Number).ToList();
                }

             

                bool check = true;
                //检测数据来源
                for (int i = 0; i < material.Count; i++)
                {
                    if ((material[0].Material.OrderNo != material[i].Material.OrderNo) && (material[0].Material.ContractNo != material[i].Material.ContractNo))
                    {
                        MessageBox.Show("所选项来源于同个订单号、合同号");
                        check = false;
                    }
                }
                if (!check)
                {
                    return;
                }
                //非标标注：件号中包含FB
                if (material[0].Material.MaterialNo.Contains("FB"))
                {
                    material.ForEach(t =>
                    {
                        if(t.Material.MaterialNo.Contains("FB"))
                        {
                            t.Material.MaterialNo = t.Material.MaterialNo.Split("-")[1];
                        }

                    });
                    return;
                }
                //货梯标志：工厂为3000
                if (material[0].Factory=="3000"&& material.Count!=1)
                {
                    PrintingXiAo_Ht(material);
                    return;
                }

                var path = System.IO.Directory.GetCurrentDirectory();
                if (!Directory.Exists(path + "/Excel"))
                {
                    Directory.CreateDirectory(path + "/Excel");
                }
                string readPath = "";
                //1 2 3 公用一个模板
                if (material.Count == 1|| material.Count == 2|| material.Count == 3 || material.Count == 4)
                {
                    readPath = path + "/Excel/" + "2行.xlsx";
                }
               
                //读取模板文件
                using (FileStream fs = System.IO.File.OpenRead(readPath))
                {
                    //获取订单信息
                    Order order = db.Orders.FirstOrDefault(t => t.OrderNo == material[0].Material.OrderNo && t.ContractNo == material[0].Material.ContractNo);
                    if (order == null)
                        return;

                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(0);
                    //1
                    IRow row = sheet.GetRow(0);
                    row.GetCell(2).SetCellValue(DateTime.Now);//日期
                                                              //5                                       
                    IRow row5 = sheet.GetRow(4);
                    row5.GetCell(0).SetCellValue("订单号:" + order.OrderNo);//订单
                    row5.GetCell(2).SetCellValue("合同号:" + (order.ContractNo));//合同号
                    row5.GetCell(10).SetCellValue(order.DeliveryDate);//交期
                    row5.GetCell(13).SetCellValue(material[0].Material.Color);//颜色


                    IRow row6 = sheet.GetRow(5);
                    IRow row7 = sheet.GetRow(6);
                    IRow row8 = sheet.GetRow(7);
                    IRow row9 = sheet.GetRow(8);
                    IRow row10 = sheet.GetRow(9);

                    //第一行
                    SetMatrial(row7, material[0], 0);
                    if(material.Count==2)
                    {
                        //第二行
                        SetMatrial(row8, material[1], 1);
                        //第三行
                        SetMatrial2(row9, material[0], 0);
                        //第四行
                        SetMatrial2(row10, material[1], 1);
                    }
                    if (material.Count == 3 || material.Count == 4)
                    {
                        //第二行
                        SetMatrial(row8, material[1], 1);
                        //第三行
                        SetMatrial(row9, material[2], 2);
                        if(material.Count==4)
                        {
                            //第三行
                            SetMatrial(row10, material[3], 3);
                        }
                    }

                  

                    //BOM参数 目前只要1
                    //SetBOM(new List<IRow>() { row6, row7, row8, row9, row10 }, obj.Materials[0].MaterialNo, obj, 0);
                    //SetBOM(new List<IRow>() {  row6, row7, row8, row9, row10 }, obj.Materials[1].MaterialNo, obj);

                    //设置绳头 钢丝绳
                    var head = db.BOMs.FindFirst(t => material.Select(f => f.Material.MaterialNo).Contains(t.MaterialNo) && !string.IsNullOrEmpty(t.WireRopeHole));
                    if (head != null)
                    {
                        row10.GetCell(12).SetCellValue($"绳头板");
                        row10.GetCell(13).SetCellValue(head.WireRopeHole);
                    }
                
                    SetPicture(material[0].Material.MaterialNo, workBook, sheet, new XSSFClientAnchor(0, 0, 0, 0, 0, 10, 12, 18));
                    if(material.Count==2)
                    {
                        SetPicture(material[1].Material.MaterialNo, workBook, sheet, new XSSFClientAnchor(0, 0, 0, 0, 0, 18, 12, 26));
                    }
                    if (material.Count == 3 || material.Count == 4)
                    {
                        SetPicture(material[2].Material.MaterialNo, workBook, sheet, new XSSFClientAnchor(0, 0, 0, 0, 0, 18, 12, 26));
                    }

                   

                    IRow row11 = sheet.GetRow(10);
                    row11.GetCell(12).SetCellValue(material[0].Material.MaterialNo);
                    List<IRow> rows = new List<IRow>();
                    for (int i = 11; i < 17; i++)
                    {
                        rows.Add(sheet.GetRow(i));
                    }
                    if (material.Count == 1||material.Count == 2)
                    {
                        SetBOM2(rows, material[0].Material.MaterialNo, material[0], excelInfo);
                    }
                    if (material.Count == 3 || material.Count == 4)
                    {
                        SetBOM2_2(rows, material[0].Material.MaterialNo, material[0], excelInfo);
                     
                    }

                    IRow row19 = sheet.GetRow(18);
                    if (material.Count != 1)
                    {
                        row19.GetCell(12).SetCellValue(material[1].Material.MaterialNo);
                    }
                      
                    List<IRow> rows2 = new List<IRow>();
                    for (int i = 19; i < 25; i++)
                    {
                        rows2.Add(sheet.GetRow(i));
                    }
                    if(material.Count==2 )
                    {
                        SetBOM2(rows2, material[1].Material.MaterialNo, material[1], excelInfo);
                    }
                    if (material.Count == 3 || material.Count == 4)
                    {
                        SetBOM2_2(rows2, material[2].Material.MaterialNo, material[2], excelInfo);
                    }
                    string allPath = path + "/Excel/" + material[0].Material.ContractNo + ".xlsx";
                    try
                    {
                        workBook.Write(System.IO.File.OpenWrite(allPath));
                    }
                    catch
                    {
                        MessageBox.Show("请先关闭之前创建的同名EXCEL文件");
                    }
                    ProcessStartInfo psi = new ProcessStartInfo
                    {
                        FileName = allPath,
                        UseShellExecute = true
                    };
                    Process.Start(psi);
                }



            }
            catch(Exception e)
            {
                MessageBox.Show("导出失败:" + e.Message);
            }

        }


        /// <summary>
        /// 西奥总表
        /// </summary>
        public static void PrintingXiAoAll(List<Order> orders)
        {
            ExcelInfo excelInfo = new ExcelInfo();
            try
            {

                var path = System.IO.Directory.GetCurrentDirectory();
                if (!Directory.Exists(path + "/Excel"))
                {
                    Directory.CreateDirectory(path + "/Excel");
                }
                string readPath = path + "/Excel/" + "zb.xlsx";
                //读取模板文件
                using (FileStream fs = System.IO.File.OpenRead(readPath))
                {
                    
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(0);
                    int index = 1;
                    //获取主梁顶置
                    var filters = db.Filters.FirstOrDefault(t => t.Company == "西奥" && t.Name.Contains("MaterialNo"));
                    //订单
                    foreach (var item in orders.GroupBy(t => t.OrderNo))
                    {
                        bool orderFlag = true;
                        //合同
                        item.ToList().ForEach(x =>
                        {
                            bool contractFlag = true;
                            decimal allweight = 0;
                            //物料
                            var m_s = db.Materials.Where(t => t.ContractNo == x.ContractNo).ToList();
                            if (filters != null)
                            {
                                var f_v = JsonConvert.DeserializeObject<FilterValue>(filters.Value);
                                if (f_v.OverHead != null)
                                {
                                    List<Material> a = new List<Material>();
                                    List<Material> b = new List<Material>();
                                    m_s.ForEach(ms =>
                                    {
                                        var head = f_v.OverHead.FindFirst(j => ms.MaterialNo.Contains(j));
                                        if (head != null)
                                        {
                                            a.Add(ms);
                                        }
                                        else
                                        {
                                            b.Add(ms);
                                        }


                                    }

                                    );
                                    a = a.OrderByDescending(o => o.L).ToList(); ;
                                    b = b.OrderByDescending(o => o.L).ToList(); ;
                                    a.AddRange(b);
                                    m_s = a;
                                }



                            }
                            else
                            {
                                m_s = m_s.OrderByDescending(t => t.L).ToList(); ;

                            }


                            int mIndex = 0;
                            m_s.ForEach(y =>
                            {
                                var row = sheet.CreateRow(index);
                                //下单日期
                                row.CreateCell(0).SetCellValue(x.SchedulingDate.ToString("M/d"));
                                //交期
                                row.CreateCell(1).SetCellValue(x.DeliveryDate.ToString("M/d"));
                                if (orderFlag)
                                {
                                    //订单号
                                    row.CreateCell(2).SetCellValue(x.OrderNo);
                                    orderFlag = false;
                                }
                                if (contractFlag)
                                {
                                    //合同号
                                    row.CreateCell(3).SetCellValue(x.ContractNo);

                                }


                                //件号
                                row.CreateCell(4).SetCellValue(y.MaterialNo);
                                //名称
                                row.CreateCell(5).SetCellValue(y.MaterialDescribe);
                                //数量
                                row.CreateCell(6).SetCellValue(y.Number.ToString());
                                var split_MaterialNo = y.MaterialNo.Contains("FB") ? y.MaterialNo.Split("-")[1] : y.MaterialNo;
                                var materia = db.Materials.FirstOrDefault(t => t.MaterialNo == y.MaterialNo && t.ContractNo == y.ContractNo);
                                //参数
                                //参数
                                if (!string.IsNullOrEmpty(materia.L) && !materia.L.Contains("L"))
                                {
                                    row.CreateCell(8).SetCellValue("L=" + materia.L);
                                }
                                else
                                {
                                    row.CreateCell(8).SetCellValue(materia.L);
                                }

                                string l = materia.L;
                                //颜色
                                row.CreateCell(9).SetCellValue(materia.Color);

                                if (y.MaterialNo.Contains("XOA2243AAA942"))
                                {

                                }
                                //备注
                                row.CreateCell(12).SetCellValue(materia.Remarks);
                                //材料
                                var bom = db.BOMs.FirstOrDefault(t => t.MaterialNo == y.MaterialNo);

                                if (bom != null)
                                {
                                    //参数补充
                                    if (string.IsNullOrEmpty(materia.L) && bom.Remark != null)
                                    {
                                        //参数
                                        if (!string.IsNullOrEmpty(bom.Remark) && !bom.Remark.Contains("L"))
                                        {
                                            row.CreateCell(8).SetCellValue("L=" + bom.Remark);
                                        }
                                        else
                                        {
                                            row.CreateCell(8).SetCellValue(bom.Remark);
                                        }

                                        l = bom.Remark.Replace("=", "").Replace("对称", "");
                                        //备注
                                        row.CreateCell(12).SetCellValue(bom.Remark);
                                    }

                                    row.CreateCell(7).SetCellValue(bom.ChannelSteel);
                                    //孔数
                                    row.CreateCell(10).SetCellValue(bom.WireRopeHole);
                                    //方向
                                    row.CreateCell(11).SetCellValue(bom.Direction);
                                    //唛头尺寸
                                    NSMaterial ns = null;
                                    if(!string.IsNullOrEmpty(bom.ChannelSteel))
                                    {
                                         ns = db.NSMaterials.FirstOrDefault(n => bom.ChannelSteel.Contains("H") ?
                                   n.Name.Contains("H") && bom.ChannelSteel.Contains(n.Type) :
                                    bom.ChannelSteel.Replace(n.Name, "") == n.Type
                                   );
                                    }
                                    else
                                    {

                                    }
                                   
                                    //if (bom.ChannelSteel.Contains("H"))
                                    //{
                                    //    return n.Name.Contains("H") && bom.ChannelSteel.Contains(n.Type);
                                    //}
                                    //else
                                    //{
                                    //    return bom.ChannelSteel.Replace(n.Name, "") == n.Type;
                                    //}
                                    if (ns != null && !string.IsNullOrEmpty(l.Trim()))
                                    {


                                        row.CreateCell(13).SetCellValue(l.Trim().Replace("L", "") + "*" + ns.Width + "*" + ns.Height);
                                        //净重
                                        var weight = Convert.ToDecimal(l.Trim().Replace("L", "")) * ns.ActualWeight * 0.001m + bom.RibbedPlateWeight * bom.RibbedPlateNumber + bom.RopeHeadWeight * bom.RopeHeadNumber;
                                        if (weight == null)
                                            weight = Convert.ToDecimal(l.Trim().Replace("L", "")) * ns.ActualWeight * 0.001m;
                                        var weight2 = Math.Round(weight.Value, 0);
                                        row.CreateCell(14).SetCellValue(Convert.ToDouble(weight2) );
                                        //毛重
                                        row.CreateCell(15).SetCellValue((Convert.ToDouble(weight2) + 0.5).ToString());
                                        allweight += weight2 * y.Number.Value;
                                        if (contractFlag)
                                        {
                                            //主件大小
                                            row.CreateCell(16).SetCellValue(l.Trim().Replace("L", "") + "*" + ns.Width + "*" + ns.Height);

                                        }
                                    }
                                    row.CreateCell(18).SetCellValue(x.Factory == "3000" ? "货梯" : "");

                                    string par = "";
                                    List<BOM_Info> pars = new List<BOM_Info>();
                                    BOMPar bOMPar = new BOMPar(x, materia, bom, formulas);
                                    if (!string.IsNullOrEmpty(bom.ChildPar) && bom.ChildPar.Contains("腰孔中心"))
                                    {
                                        par = bom.ChildPar;
                                        pars = JsonConvert.DeserializeObject<List<BOM_Info>>(par);
                                        var key = pars.FirstOrDefault(t => t.Key == "腰孔中心");
                                        var value = bOMPar.FindFormulas(key);
                                        row.GetCell(12).SetCellValue(value);
                                    }
                                    else if (!string.IsNullOrEmpty(bom.OtherParJson))
                                    {
                                        par = bom.OtherParJson;
                                        List<OtherParJson> otherPars = JsonConvert.DeserializeObject<List<OtherParJson>>(bom.OtherParJson);
                                        OtherParJson otherPar = otherPars.FirstOrDefault(t => t.Name == "3");
                                        if (otherPar != null)
                                        {
                                            pars = JsonConvert.DeserializeObject<List<BOM_Info>>(otherPar.Par);
                                            if (mIndex < pars.Count)
                                            {
                                                var key = pars[mIndex];
                                                var value = bOMPar.FindFormulas(key);
                                                row.GetCell(12).SetCellValue(value);
                                            }
                                        }
                                    }
                                }
                                mIndex++;
                                contractFlag = false;
                                index++;




                            });



                            //补充合同重量
                            if (allweight != 0)
                            {
                                sheet.GetRow(index - m_s.Count).CreateCell(17).SetCellValue((Convert.ToDouble(Math.Round(allweight, 0))));
                            }








                        });

                    }
                    string allPath = path + "/Excel/" + DateTime.Now.ToString("yyyymmddhhMM") + "zb.xlsx";
                    try
                    {
                        workBook.Write(System.IO.File.OpenWrite(allPath));
                    }
                    catch(Exception e)
                    {
                        MessageBox.Show("请先关闭之前创建的同名EXCEL文件");
                    }
                    ProcessStartInfo psi = new ProcessStartInfo
                    {
                        FileName = allPath,
                        UseShellExecute = true
                    };
                    Process.Start(psi);

                }



            }
            catch (Exception e)
            {

                MessageBox.Show("导出失败:" + e.Message);
            }

        }


        public static void PrintingXiAo_Ht(List<MaterialViewModel> material)
        {
            try
            {
                var path = System.IO.Directory.GetCurrentDirectory();
                if (!Directory.Exists(path + "/Excel"))
                {
                    Directory.CreateDirectory(path + "/Excel");
                }
                string readPath = "";
                readPath = path + "/Excel/" + "ht.xlsx";

                //读取模板文件
                using (FileStream fs = System.IO.File.OpenRead(readPath))
                {
                    //获取订单信息
                    Order order = db.Orders.FirstOrDefault(t => t.OrderNo == material[0].Material.OrderNo && t.ContractNo == material[0].Material.ContractNo);
                    if (order == null)
                        return;

                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(0);
                    //3
                    IRow row3 = sheet.GetRow(2);
                    row3.GetCell(8).SetCellValue(order.SchedulingDate.ToShortDateString());//排产日期
                    row3.GetCell(9).SetCellValue(DateTime.Now.ToShortDateString());//日期
                                                                                               
               

                    IRow row5 = sheet.GetRow(4);
                    IRow row6 = sheet.GetRow(5);
                    IRow row7 = sheet.GetRow(6);
                    IRow row8 = sheet.GetRow(7);
                    IRow row9 = sheet.GetRow(8);
                    IRow row10 = sheet.GetRow(9);
                    IRow row11 = sheet.GetRow(10);
                    List<IRow> rows_top = new List<IRow>() { row5, row6, row7, row8, row9, row10, row11 };

                    IRow row14 = sheet.GetRow(13);
                    IRow row15 = sheet.GetRow(14);
                    IRow row16 = sheet.GetRow(15);
                    IRow row17 = sheet.GetRow(16);
                    IRow row18 = sheet.GetRow(17);
                    IRow row19 = sheet.GetRow(18);
              
                    List<IRow> rows_bottom = new List<IRow>() { row14, row15, row16, row17, row18, row19 };
                    for (int i = 0; i < material.Count; i++)
                    {
                        var bom = db.BOMs.FirstOrDefault(t => t.MaterialNo == material[i].Material.MaterialNo);
                        if (bom != null)
                        {
                            var otherParJson = JsonConvert.DeserializeObject<List<OtherParJson>>(bom.OtherParJson);
                            var pars = JsonConvert.DeserializeObject < List < BOM_Info >>(otherParJson.FirstOrDefault(t => t.Name == "ht").ChildPar) ;
                            //序号
                            rows_top[i].Cells[0].SetCellValue(i + 1);
                            //交期
                            rows_top[i].Cells[1].SetCellValue(order.DeliveryDate);
                            //合同号
                            rows_top[i].Cells[2].SetCellValue(order.ContractNo);
                            //外购件号
                            rows_top[i].Cells[3].SetCellValue(material[i].Material.MaterialNo);
                            //名称
                            rows_top[i].Cells[4].SetCellValue(material[i].Material.MaterialDescribe);
                            //数量
                            rows_top[i].Cells[5].SetCellValue(material[i].Material.Number.ToString());
                            //材料
                            rows_top[i].Cells[6].SetCellValue(bom.ChannelSteel);
                            //参数
                            rows_top[i].Cells[7].SetCellValue(material[i].Material.MaterialSpecifications);
                            //颜色
                            rows_top[i].Cells[8].SetCellValue(material[i].Material.Color);
                            //备注
                            rows_top[i].Cells[9].SetCellValue(pars.FirstOrDefault(t => t.Key == "备注").Value);


                            //序号
                            rows_bottom[i].Cells[0].SetCellValue(i + 1);
                            //内部件号
                            rows_bottom[i].Cells[1].SetCellValue(material[i].Material.MaterialNo);
                            //名称
                            rows_bottom[i].Cells[3].SetCellValue(material[i].Material.MaterialDescribe);
                            //参1
                            rows_bottom[i].Cells[4].SetCellValue(pars[1].Key);
                        
                            rows_bottom[i].Cells[5].SetCellValue(SetXiao_HtValue(pars[1].Key, order, material[i].Material, bom));
                            //参2
                            rows_bottom[i].Cells[6].SetCellValue(pars[2].Key);
                            rows_bottom[i].Cells[7].SetCellValue(SetXiao_HtValue(pars[2].Key, order, material[i].Material, bom));
                            //参3
                            rows_bottom[i].Cells[8].SetCellValue(pars[3].Key);
                            rows_bottom[i].Cells[9].SetCellValue(SetXiao_HtValue(pars[3].Key, order, material[i].Material, bom));

                        }
                            

                    }
                   

                 
                    string allPath = path + "/Excel/" + material[0].Material.ContractNo + ".xlsx";
                    try
                    {
                        workBook.Write(System.IO.File.OpenWrite(allPath));
                    }
                    catch
                    {
                        MessageBox.Show("请先关闭之前创建的同名EXCEL文件");
                    }
                    ProcessStartInfo psi = new ProcessStartInfo
                    {
                        FileName = allPath,
                        UseShellExecute = true
                    };
                    Process.Start(psi);
                }
            }
            catch(Exception e)
            {

            }
        }


       







        private static string SetXiao_HtValue(string par,Order order, Material material, BOM bom)
        {
            //提前进行数据替换
            par = par.Replace("井道净深", "HD1").Replace("A", "CarOuterWidth").Replace("轿厢中心到井道后壁的距离HD2", "HD2").Replace("L1", "HW-DR2")
                     .Replace("CTCR", "DR1").Replace("H1", "HD1-HD2").Replace("=","");

          
            BOMPar bOMPar = new BOMPar(order, material, bom);
            par = bOMPar.FindFormulas(new BOM_Info() { Key = "", Cal = par });

            return par;
        }

        private static void SetPicture(string name, IWorkbook workBook, ISheet sheet, XSSFClientAnchor anchor)
        {
            // 读取要添加的图片文件
            if (File.Exists($@"\\192.168.0.157\新建文件夹\西奥\图库\{name}.png"))
            {
                byte[] bytes = File.ReadAllBytes($@"\\192.168.0.157\新建文件夹\西奥\图库\{name}.png");
                // 创建图片patriarch
                XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                
                int ip = workBook.AddPicture(bytes, PictureType.PNG);
                XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, ip);
            }
            else
            {
                MessageBox.Show($@"{name}缺少图纸");
            }
        }

      


        private static void SetMatrial(IRow row, MaterialViewModel material, int index)
        {
            //判断是否是非标
            string materialNo = material.Material.MaterialNo;
            if (material.Material.MaterialNo.Contains("FB"))
            {
                materialNo = materialNo.Split('-')[1];
            }
            //序号
            row.GetCell(0).SetCellValue(index + 1);
            //物料编号

            row.GetCell(1).SetCellValue(SplitMaterialNo(materialNo));
            //查询BOM的时候必须要用原本物料编码
            var bom = db.BOMs.FindFirst(t => t.MaterialNo.Contains(materialNo));
            //在查询物料时也可使用非标查询
            var mat = db.Materials.FindFirst(t => t.MaterialNo == material.Material.MaterialNo && t.ContractNo == material.Material.ContractNo);
            if (bom != null)
            {
                //材料
                row.GetCell(2).SetCellValue(bom.ChannelSteel);
                //数量
                row.GetCell(3).SetCellValue((double)mat.Number);
                //备注
                row.GetCell(4).SetCellValue(bom.AccessoriesRemark);
                //L=
                row.GetCell(6).SetCellValue(mat.L);
            }


        }

        /// <summary>
        /// 查询子件信息
        /// </summary>
        /// <param name="row"></param>
        /// <param name="material"></param>
        /// <param name="index"></param>
        private static void SetMatrial2(IRow row, MaterialViewModel material, int index)
        {
            string materialNo = material.Material.MaterialNo;
            if (material.Material.MaterialNo.Contains("FB"))
            {
                materialNo = materialNo.Split('-')[1];
            }
            var mat = db.Materials.FindFirst(t => t.MaterialNo.Contains(materialNo));
            var bom = db.BOMs.FindFirst(t => t.MaterialNo == materialNo);
            //序号
            row.GetCell(0).SetCellValue(index + 3);

            if (bom != null)
            {
                // 辅料编号
                row.GetCell(1).SetCellValue(SplitMaterialNo(bom.Accessories));
                // 辅料规格
                row.GetCell(2).SetCellValue(SplitMaterialNo(bom.AccessoriesSize));
                // 辅料数量
                row.GetCell(3).SetCellValue(SplitMaterialNo(bom.AccessoriesNumner));
                // 辅料方式
                row.GetCell(4).SetCellValue(SplitMaterialNo(bom.AccessoriesHandle));
            }


        }

        /// <summary>
        /// 2行
        /// </summary>
        /// <param name="rows"></param>
        /// <param name="materialNo"></param>
        /// <param name="material"></param>
        private static void SetBOM2(List<IRow> rows, string materialNo, MaterialViewModel material, ExcelInfo excelInfo)
        {

            //查询BOM参数
            excelInfo.XiaoAo2Row = new List<XiaoAo2Row>();
         
            BOM bom = db.BOMs.FirstOrDefault(t => t.MaterialNo.Contains(materialNo) || materialNo.Contains(t.MaterialNo));
            Order order = db.Orders.FirstOrDefault(t => t.ContractNo == material.Material.ContractNo);
            if (bom != null)
            {
                BOMPar bOMPar = new BOMPar(order, material.Material, bom);
                string par = string.IsNullOrEmpty(bom.ChildPar) ? bom.Par : bom.ChildPar;
                if (!string.IsNullOrEmpty(par))
                {
                    List<BOM_Info> pars = JsonConvert.DeserializeObject<List<BOM_Info>>(par);
                    for (int i = 0; i < pars.Count; i++)
                    {

                        rows[i].GetCell(12).SetCellValue(pars[i].Key);
                        string val = pars[i].Value;
                        if (string.IsNullOrEmpty(val))
                        {
                            //对2行非基础参数进行赋值
                            pars[i].Cal=  bOMPar.Set2Value( pars[i].Cal);
                            val = bOMPar.FindFormulas(pars[i]);
                        }
                        //excelInfo.XiaoAo2Row.KeyValues.Add(new KeyValue<string>() { Key = pars[i].Key, Value= val });
                       rows[i].GetCell(14).SetCellValue(val);

                    }
                }

            }




        }

   

        /// <summary>
        /// 三行
        /// </summary>
        /// <param name="rows"></param>
        /// <param name="materialNo"></param>
        /// <param name="material"></param>
        private static void SetBOM2_2(List<IRow> rows, string materialNo, MaterialViewModel material, ExcelInfo excelInfo)
        {

            //查询BOM参数

            BOM bom = db.BOMs.FirstOrDefault(t => t.MaterialNo.Contains(materialNo) || materialNo.Contains(t.MaterialNo));
            Order order = db.Orders.FirstOrDefault(t => t.ContractNo == material.Material.ContractNo);
            if (bom != null&& !string.IsNullOrEmpty(bom.OtherParJson))
            {
                BOMPar bOMPar = new BOMPar(order, material.Material, bom, formulas);
            
                List<OtherParJson> otherPars = JsonConvert.DeserializeObject<List<OtherParJson>>(bom.OtherParJson);
                OtherParJson otherPar = otherPars.FirstOrDefault(t => t.Name == "3");
                if (otherPar!=null)
                {
                   
                    List<BOM_Info> pars = JsonConvert.DeserializeObject<List<BOM_Info>>(string.IsNullOrEmpty(otherPar.ChildPar) ? otherPar.Par : otherPar.ChildPar);
                    for (int i = 0; i < pars.Count; i++)
                    {

                        rows[i].GetCell(12).SetCellValue(pars[i].Key);
                        string val = pars[i].Value;
                        if (string.IsNullOrEmpty(val))
                        {
                            //三行公式提前分解
                            if (pars[i].Cal.Contains("G16"))
                            {
                                pars[i].Cal = pars[i].Cal.Replace("G16", material.Material.L);
                            }
                            //
                            if (pars[i].Cal.Contains("DZ_V"))
                            {
                                if(string.IsNullOrEmpty(bom.ParJson))
                                {
                                    pars[i].Cal = pars[i].Cal.Replace("DZ_V", "DZ");
                                }
                                else
                                {
                                    var parjsons = JsonConvert.DeserializeObject<List<KeyValue<string>>>(bom.ParJson);
                                    var prajson = parjsons.FirstOrDefault(t => t.Key == order.CounterweightArrange);
                                    if (prajson != null)
                                    {
                                        pars[i].Cal = pars[i].Cal.Replace("DZ_V", $"({prajson.Value})");
                                    }
                                }
                              
                       
                            }
                            



                            val = bOMPar.FindFormulas(pars[i]);
                        }
                        rows[i].GetCell(14).SetCellValue(val);

                    }
                }

            }




        }

        private static string SplitMaterialNo(string no)
        {

            if (string.IsNullOrEmpty(no))
                return "";

            var list = no.Split("-");
            if (list.Count() == 3)
            {
                return list[1];
            }
            else
            {
                return no;
            }
        }


        /// <summary>
        /// BOM主表导入
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM1(string fileName)
        {
            try
            {
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(1);

                    var dataTabale = new DataTable();

                    for (global::System.Int32 i = 1; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet.GetRow(i).GetCell(0).CellToString();
                        var bOM = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bOM == null)
                        {
                            bOM = new BOM();
                        }
                    
                        //bOM.MaterialNo = sheet.GetRow(i).GetCell(0).CellToString();
                        bOM.ChildMaterialNo = sheet.GetRow(i).GetCell(1).CellToString();
                        bOM.ChannelSteel = sheet.GetRow(i).GetCell(2).CellToString();
                        bOM.ChannelSteelID = FindNSID(bOM.ChannelSteel);
                        bOM.Weight = sheet.GetRow(i).GetCell(3).CellToDouble();
                        bOM.RibbedPlateNo = sheet.GetRow(i).GetCell(4).CellToString();
                        bOM.RibbedPlateType = sheet.GetRow(i).GetCell(5).CellToString();
                        bOM.RibbedPlateNumber = sheet.GetRow(i).GetCell(6).CellToDouble();
                        bOM.RibbedPlateWeight = CalWeight(bOM.RibbedPlateType);
                        bOM.RopeHeadNo = sheet.GetRow(i).GetCell(8).CellToString();
                        bOM.RopeHeadType = sheet.GetRow(i).GetCell(9).CellToString();
                        bOM.RopeHeadNumber = sheet.GetRow(i).GetCell(10).CellToDouble();
                        bOM.RopeHeadWeight = CalWeight(bOM.RopeHeadType);
                        bOM.Counterweight = bOM.RibbedPlateWeight * bOM.RibbedPlateNumber + bOM.RopeHeadWeight * bOM.RopeHeadNumber;
                        if (!string.IsNullOrEmpty(no) && string.IsNullOrEmpty(bOM.MaterialNo))
                        {
                            bOM.MaterialNo = no;
                            db.BOMs.Add(bOM);
                        }
                      

                    }
                    db.SaveChanges();





                }
            }
            catch (Exception e)
            {


            }
        }
        /// <summary>
        /// BOM从表导入
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM2(string fileName)
        {
            try
            {
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(2);

                    var dataTabale = new DataTable();

                    for (global::System.Int32 i = 1; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet.GetRow(i).GetCell(0).CellToString();
                        var bom = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bom == null)
                        {
                            bom = new BOM();
                        }
                        bom.CarRoofQheelDiameter = sheet.GetRow(i).GetCell(2).CellToDouble();
                        bom.WireRopeHole = sheet.GetRow(i).GetCell(3).CellToString();
                        bom.HostPosition = sheet.GetRow(i).GetCell(4).CellToString();
                        bom.MakeDirection = sheet.GetRow(i).GetCell(5).CellToString();
                        bom.Direction = bom.MakeDirection.Contains("对称制作") ? "反" : "";
                        bom.Remark = sheet.GetRow(i).GetCell(7).CellToString();
                        var f_bom = db.BOMs.FirstOrDefault(t => t.ChildMaterialNo == no && string.IsNullOrEmpty(t.ChannelSteel));
                        if (f_bom != null)
                        {
                            f_bom.ChannelSteel = sheet.GetRow(i).GetCell(1).CellToString();
                            f_bom.ChannelSteelID = FindNSID(f_bom.ChannelSteel);
                        }

                        if (!string.IsNullOrEmpty(no) && string.IsNullOrEmpty(bom.MaterialNo))
                        {
                            bom.MaterialNo = no;
                            db.BOMs.Add(bom);
                        }
                    }
                    db.SaveChanges();





                }
            }
            catch (Exception e)
            {


            }
        }
        /// <summary>
        /// BOM参数
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM3(string fileName)
        {
            try
            {
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(1);

                    var dataTabale = new DataTable();

                    for (global::System.Int32 i = 3; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet.GetRow(i).GetCell(1).CellToString();
                        var bom = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bom == null)
                        {
                            bom = new BOM();
                        }
                        if (bom.MaterialNo == "XOA2243AFD998")
                        {

                        }

                        List<int> ints = new List<int>() { 2, 4, 6, 8, 10, 12, 14 };
                        List<BOM_Info> info = new List<BOM_Info>();
                        //获取当前行的所有地址
                        List<string> address = new List<string>();
                        row.Cells.ForEach(t => address.Add(t.Address.ToString()));
                        //录入所有非公式的常量值  以及公式的名称
                        for (int j = 0; j < ints.Count(); j++)
                        {
                            var name = row.GetCell(ints[j]).CellToString();
                            if (!string.IsNullOrEmpty(name))
                            {
                                BOM_Info bOM_Info = new BOM_Info();
                                bOM_Info.Key = name;
                                var cell = row.GetCell(ints[j] + 1);
                                if (cell != null)
                                {
                                    //对公式进行分解
                                    if (cell.CellType == CellType.Formula)
                                    {
                                        bOM_Info.Cal = FindDataFromSheet(row, cell);
                                        //if (string.IsNullOrEmpty(bOM_Info.Cal))
                                        //    bOM_Info.Value = cell.ToString();
                                    }
                                    else
                                    {
                                        bOM_Info.Value = cell.CellToString();
                                    }

                                  
                                }
                                info.Add(bOM_Info);
                            }

                        }
                        List<BOM_Info> info2 = new List<BOM_Info>();
                        List<int> ints2 = new List<int>() { 16, 18, 20, 22, 24, 26, 28, 30, 32, 34 };
                        for (int j = 0; j < ints2.Count(); j++)
                        {
                            var name = row.GetCell(ints2[j]).CellToString();
                            if (!string.IsNullOrEmpty(name))
                            {

                                BOM_Info bOM_Info = new BOM_Info();
                                bOM_Info.Key = name;
                                if (row.GetCell(ints2[j] + 1) != null && row.GetCell(ints2[j] + 1).CellType != CellType.Formula)
                                {
                                    bOM_Info.Value = row.GetCell(ints2[j] + 1).CellToString();
                                }
                                info2.Add(bOM_Info);
                            }


                        }
                        bom.ChildPar = JsonConvert.SerializeObject(info);
                        bom.Par = JsonConvert.SerializeObject(info2);
                        //辅料信息
                        bom.AccessoriesRemark = row.GetCell(36).CellToString();
                        bom.Accessories = row.GetCell(37).CellToString();
                        bom.AccessoriesSize = row.GetCell(38).CellToString();
                        bom.AccessoriesNumner = row.GetCell(39).CellToString();
                        bom.AccessoriesHandle = row.GetCell(40).CellToString();

                        if (!string.IsNullOrEmpty(no)&&string.IsNullOrEmpty(bom.MaterialNo))
                        {
                            bom.MaterialNo = no;
                            db.BOMs.Add(bom);
                        }
                    }

                    db.SaveChanges();





                }
            }
            catch (Exception e)
            {


            }
        }

        /// <summary>
        /// BOM参数 3行 模板
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM4(string fileName)
        {
       
            try
            {
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(2);
                    var dataTabale = new DataTable();
                    for (global::System.Int32 i = 3; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet.GetRow(i).GetCell(1).CellToString();
                        var bom = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bom == null)
                        {
                            bom = new BOM();
                        }
                        if(no== "XOA4134ABZ954")
                        {
                            string ccc="";
                         }


                        List<int> ints = new List<int>() { 2, 4, 6, 8, 10, 12, 14 };
                        List<BOM_Info> info = new List<BOM_Info>();
                        //获取当前行的所有地址
                        List<string> address = new List<string>();
                        row.Cells.ForEach(t => address.Add(t.Address.ToString()));
                        //录入所有非公式的常量值  以及公式的名称
                        for (int j = 0; j < ints.Count(); j++)
                        {
                            var name = row.GetCell(ints[j]).CellToString();
                            if (!string.IsNullOrEmpty(name))
                            {
                                BOM_Info bOM_Info = new BOM_Info();
                                bOM_Info.Key = name;
                                var cell = row.GetCell(ints[j] + 1);
                                if (cell != null)
                                {
                                    //对公式进行分解
                                    if (cell.CellType == CellType.Formula)
                                    {
                                        bOM_Info.Cal = FindDataFromSheet(row, cell);
                                        //if (string.IsNullOrEmpty(bOM_Info.Cal))
                                        //    bOM_Info.Value = cell.ToString();
                                    }
                                    else
                                    {
                                        bOM_Info.Value = cell.CellToString();
                                    }


                                }
                                info.Add(bOM_Info);
                            }

                        }
                        List<BOM_Info> info2 = new List<BOM_Info>();
                        List<int> ints2 = new List<int>() { 16, 18, 20, 22, 24, 26, 28, 30, 32, 34 };
                        for (int j = 0; j < ints2.Count(); j++)
                        {
                            var name = row.GetCell(ints2[j]).CellToString();
                        
                            if (!string.IsNullOrEmpty(name))
                            {

                                BOM_Info bOM_Info = new BOM_Info();
                                bOM_Info.Key = name;
                                var cell = row.GetCell(ints2[j] + 1);
                                if (cell != null)
                                {
                                    //对公式进行分解
                                    if (cell.CellType == CellType.Formula)
                                    {
                                        bOM_Info.Cal = FindDataFromSheet(row, cell);
                                        //if (string.IsNullOrEmpty(bOM_Info.Cal))
                                        //    bOM_Info.Value = cell.ToString();
                                    }
                                    else
                                    {
                                        bOM_Info.Value = cell.CellToString();
                                    }
                                }
                                    
                                info2.Add(bOM_Info);
                            }


                        }
                        //获取三行  货梯参数
                        List<OtherParJson> otherParJsons = new List<OtherParJson>();
                        if(!string.IsNullOrEmpty(bom.OtherParJson))
                        {
                            otherParJsons = JsonConvert.DeserializeObject<List<OtherParJson>>(bom.OtherParJson);
                        }
                        //修改
                        var san = otherParJsons.FirstOrDefault(t => t.Name == "3");
                        if (san==null)
                        {
                            san=new OtherParJson() { 
                                Name="3",
                                ChildPar = JsonConvert.SerializeObject(info),
                                Par= JsonConvert.SerializeObject(info2)
                            };
                            otherParJsons.Add(san);
                        }
                        else
                        {
                            san.ChildPar = JsonConvert.SerializeObject(info);
                            san.Par = JsonConvert.SerializeObject(info2);

                        }
                        bom.OtherParJson=JsonConvert.SerializeObject(otherParJsons);


                        if (!string.IsNullOrEmpty(no) && string.IsNullOrEmpty(bom.MaterialNo))
                        {
                            bom.MaterialNo = no;
                            db.BOMs.Add(bom);
                        }
                    }
                    db.SaveChanges();
                }
            }
            catch (Exception e)
            {


            }
        }


        /// <summary>
        /// BOM参数 3行 源
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM5(string fileName)
        {

            try
            {
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheetAt(1);
                    var dataTabale = new DataTable();

                    //获取编号
                    var row = sheet.GetRow(11);
                    var no= row.Cells.Select((t, Index) =>
                    {
                        if (Index >= 28 && Index <= 64 && Index != 44)
                            return t;
                        return null;
                    }).ToList();

                    //
                    for(int i = 0; i < no.Count; i++)
                    {
                       
                        var t = no[i];
                        if (t == null)
                        {
                            continue;
                        }

                        var bom = db.BOMs.FirstOrDefault(j => j.MaterialNo == t.CellToString());
                        if (bom == null)
                        {
                            bom = new BOM();
                        }
                        List<KeyValue<string>> kv_list = new List<ToolClass.KeyValue<string>>();
                        if (i<44)
                        {
                            AddValue(kv_list, sheet.GetRow(12).Cells[i], "后置2区");
                            AddValue(kv_list, sheet.GetRow(13).Cells[i], "后侧右置");
                            AddValue(kv_list, sheet.GetRow(14).Cells[i], "左侧后置");
                            AddValue(kv_list, sheet.GetRow(15).Cells[i], "右侧前置");
                            AddValue(kv_list, sheet.GetRow(16).Cells[i], "后置1区");
                            AddValue(kv_list, sheet.GetRow(17).Cells[i], "后侧左置");
                            AddValue(kv_list, sheet.GetRow(18).Cells[i], "左侧前置");
                            AddValue(kv_list, sheet.GetRow(19).Cells[i], "右侧后置");                     
                        }
                        else
                        {
                            AddValue(kv_list, sheet.GetRow(13).Cells[i], "后置(对中心)");
                            AddValue(kv_list, sheet.GetRow(13).Cells[i], "后侧中置");
                            AddValue(kv_list, sheet.GetRow(14).Cells[i], "左侧中置");
                            AddValue(kv_list, sheet.GetRow(15).Cells[i], "右侧中置");
                            AddValue(kv_list, sheet.GetRow(15).Cells[i], "侧置(右)");
                            AddValue(kv_list, sheet.GetRow(14).Cells[i], "侧置(左)");
                        }
                        bom.ParJson = JsonConvert.SerializeObject(kv_list);
                        if (!string.IsNullOrEmpty(t.CellToString()) && string.IsNullOrEmpty(bom.MaterialNo))
                        {
                            bom.MaterialNo = t.CellToString();
                            db.BOMs.Add(bom);
                        }
                        db.SaveChanges();

                    

                    }
                





                }
            }
            catch (Exception e)
            {


            }
        }

        /// <summary>
        /// 货梯数据导入
        /// </summary>
        /// <param name="fileName"></param>
        public static void ReadBOM6(string fileName)
        {

            try
            {
                using (FileStream fs = System.IO.File.OpenRead(fileName))
                {
                    //【1】.创建工作簿 2007之前用HSSFWorkbook 2007之后用6XSSFWorkbook  
                    IWorkbook? workBook = null;
                    // 根据版本号创建不同版本的excel
                    workBook = new XSSFWorkbook(fs);
                    // 获取工作表
                    ISheet sheet = workBook.GetSheet("BOM");
                    var dataTabale = new DataTable();
                    for (global::System.Int32 i = 1; i < sheet.LastRowNum; i++)
                    {
                        var row = sheet.GetRow(i);
                        if (row == null)
                            continue;
                        //获取编号
                        string no = sheet.GetRow(i).GetCell(0).CellToString();
                        var bom = db.BOMs.FirstOrDefault(t => t.MaterialNo == no);
                        //存在则补全 //不存在则新增
                        if (bom == null)
                        {
                            bom = new BOM();
                        }
                        if (no == "XOA4134ABZ954")
                        {
                            string ccc = "";
                        }


                        List<int> ints = new List<int>() {1, 3, 4, 5 };
                        List<BOM_Info> info = new List<BOM_Info>();
                        //获取当前行的所有地址
                        List<string> address = new List<string>();
                        row.Cells.ForEach(t => address.Add(t.Address.ToString()));
                        //获取公式
                        for (int j = 0; j < ints.Count(); j++)
                        {
                            var name = row.GetCell(ints[j]).CellToString();
                            if (!string.IsNullOrEmpty(name))
                            {
                                BOM_Info bOM_Info = new BOM_Info();
                            
                                if (ints[j]==1)
                                {
                                    bOM_Info.Key = "备注";
                                    bOM_Info.Value = row.GetCell(ints[j]).CellToString();
                                }
                                else
                                {
                                    bOM_Info.Key = name;
                                }
                             
                                info.Add(bOM_Info);
                            }

                        }


                        //获取三行  货梯参数
                        List<OtherParJson> otherParJsons = new List<OtherParJson>();
                        if (!string.IsNullOrEmpty(bom.OtherParJson))
                        {
                            otherParJsons = JsonConvert.DeserializeObject<List<OtherParJson>>(bom.OtherParJson);
                        }
                        //修改
                        var ht = otherParJsons.FirstOrDefault(t => t.Name == "ht");
                        if (ht == null)
                        {
                            ht = new OtherParJson()
                            {
                                Name = "ht",
                                ChildPar = JsonConvert.SerializeObject(info),
                            
                            };
                            otherParJsons.Add(ht);
                        }
                        else
                        {
                            ht.ChildPar = JsonConvert.SerializeObject(info);
                        }
                        bom.OtherParJson = JsonConvert.SerializeObject(otherParJsons);

                        if (!string.IsNullOrEmpty(no) && string.IsNullOrEmpty(bom.MaterialNo))
                        {
                            bom.MaterialNo = no;
                            db.BOMs.Add(bom);
                        }
                    }
                    db.SaveChanges();
                }
                //货梯计算公式导入
                //Model.Formula formula = new Model.Formula() { Name = "货梯集合" };
                //List<BOM_Info> hts = new List<BOM_Info>();
                //hts.Add(new BOM_Info() { Key = "井道净宽", Cal = "HW" });
                //hts.Add(new BOM_Info() { Key = "井道净深", Cal = "HD1" });
                //hts.Add(new BOM_Info() { Key = "轿厢中心到井道后壁的距离HD2", Cal = "HW" });
                //hts.Add(new BOM_Info() { Key = "轿厢中心到左边井道壁距离", Cal = "HW1" });
                //hts.Add(new BOM_Info() { Key = "CTCR-200=", Cal = "DR1-200" });
               
                //hts.Add(new BOM_Info() { Key = "CTCR-200=", Cal = "DR1-200" });
                //hts.Add(new BOM_Info() { Key = "HW2+170=", Cal = "DR2+170" });
                //hts.Add(new BOM_Info() { Key = "A/2-420=", Cal = "CarOuterWidth/2-420" });
                //hts.Add(new BOM_Info() { Key = "H1+200=", Cal = "HW-HD1-200" });
                //hts.Add(new BOM_Info() { Key = "A/2-418=", Cal = "CarOuterWidth/2-418" });

                //hts.Add(new BOM_Info() { Key = "H1+150=", Cal = "HW-HD1+150" });
                //hts.Add(new BOM_Info() { Key = "A/2+425=", Cal = "CarOuterWidth/2+425" });
                //hts.Add(new BOM_Info() { Key = "L1+150=", Cal = "HW-DR2+150" });//s25
                //hts.Add(new BOM_Info() { Key = "参数1+参数2=", Cal = "CarOuterWidth/2+425+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "A/2-50=", Cal = "CarOuterWidth/2-50" });//s27

                //hts.Add(new BOM_Info() { Key = "尺寸线2=", Cal = "arOuterWidth/2-50+HW-DR2+150" });//s28
                //hts.Add(new BOM_Info() { Key = "A/2+400=", Cal = "CarOuterWidth/2+400" });//s29
                //hts.Add(new BOM_Info() { Key = "尺寸线3=", Cal = "CarOuterWidth/2+400+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "A/2-64=", Cal = "CarOuterWidth/2-64" });//s31
                //hts.Add(new BOM_Info() { Key = "尺寸线1=", Cal = "CarOuterWidth/2-64+HW-DR2+150" });


                //hts.Add(new BOM_Info() { Key = "CTCR-240=", Cal = "DR1-240" });
                //hts.Add(new BOM_Info() { Key = "A/2-420+L1+150+40=", Cal = "CarOuterWidth/2-420+HW-DR2+150+40" });
                //hts.Add(new BOM_Info() { Key = "CTCR-200+L1+150=", Cal = "DR1-200+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "A/2-403=", Cal = "CarOuterWidth/2-403" });
                //hts.Add(new BOM_Info() { Key = "A/2-403+L1+150+40=", Cal = "CarOuterWidth/2-403+HW-DR2+150+40" });

                //hts.Add(new BOM_Info() { Key = "HW+340=", Cal = "HW+340" });
                //hts.Add(new BOM_Info() { Key = "L1+170+A/2-64=", Cal = "HW-DR2+170+CarOuterWidth/2-64" });
                //hts.Add(new BOM_Info() { Key = "CTCR-200+L1+150=", Cal = "DR1-200+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "A/2-383=", Cal = "CarOuterWidth/2-383" });
                //hts.Add(new BOM_Info() { Key = "A/2-383+L1+150+40=", Cal = "CarOuterWidth/2-383+HW-DR2+150+40" });

                //hts.Add(new BOM_Info() { Key = "H1+WT/2+60=", Cal = "HD1-HW+WT/2+60" });
                //hts.Add(new BOM_Info() { Key = "L1+150+A/2-64=", Cal = "HW-DR2+150+CarOuterWidth/2-64" });
                //hts.Add(new BOM_Info() { Key = "A/2-15=", Cal = "CarOuterWidth/2-15" });
                //hts.Add(new BOM_Info() { Key = "A/2-15+L1+150=", Cal = "CarOuterWidth/2-15+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "CTCR-260=", Cal = "DR1-260" });

                //hts.Add(new BOM_Info() { Key = "CTCR-260+L1+150=", Cal = "DR1-260+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "A/2+45+L1+150=", Cal = "CarOuterWidth/2+45+HW-DR2+150" });
                //hts.Add(new BOM_Info() { Key = "A/2-383+L1+150=", Cal = "CarOuterWidth/2+383+HW-DR2+150" });



            }
            catch (Exception e)
            {


            }
        }

        private static void AddValue(List<KeyValue<string>> kv_list,NPOI.SS.UserModel.ICell cell,string name)
        {
          
            if (!string.IsNullOrEmpty(cell.ToString()))
                kv_list.Add(new KeyValue<string> { Key = name, Value = cell.CellFormula });
        }

        public static string FindDataFromSheet(IRow row, ICell cell)
        {
            string result = "";
            string calStr = cell.ToString();
            string look="";
            try
            {
                //检测Excel字符串
                if (calStr.Contains("&"))
                {
                    //获取开始结束位置
                    int begin = calStr.IndexOf("&");
                    int end = calStr.IndexOf("&", begin + 1);
                    if (end == -1)
                        end = calStr.Length;
                    //获取这段字符
                    look = calStr.Substring(begin, end - begin + calStr.Where(t=>t== '&').Count()-1);
                    var excelStr = row.Cells.ToList().FindFirst(t => t.Address.ToString() == look.Replace("&", ""));
                    if (excelStr != null)
                    {
                        look = excelStr.CellToString();
                    }
                    calStr = calStr.Insert(begin, "^");
                    calStr = calStr.Remove(begin + 1, end - begin + calStr.Where(t => t == '&').Count() - 1);


                }

                calStr = ExcelPreDeal(calStr);

            }
            catch { 
            
            }


            //分解公式
            string[] arr = ["+", "-", "*", "/", "(", ")", "^"];
            //值
            var names = calStr.Split(arr,  StringSplitOptions.None);
            //运算符
            var op0 = calStr.Where(t => arr.Contains(t.ToString())).ToList();
            List<string> op = new List<string>();
            op0.ForEach(t => { op.Add(t.ToString()); });
            foreach (var name in names.Select((source, index) => new { source, index }))
            {
             
                if (op.Count > name.index)
                {
                    if (op[name.index] == "^")
                        op[name.index] = $"|str:{look}|";

                        //乘除的运算优先级高
                        if (op[name.index] == "*" || op[name.index] == "/")
                    {
                      
                        if (!string.IsNullOrEmpty(name.source))
                        {
                            result += $"({GetRowValue(row, name.source)})" + op[name.index];
                        }
                        else
                        {
                            result += op[name.index];
                        }

                    }
                    else
                    {
                        result += GetRowValue(row, name.source) + op[name.index];
                    }


                }
                else
                {
                    result += GetRowValue(row, name.source);

                }
            }
            return result;
        }

        public static string GetRowValue(IRow row, string address)
        {
            string result = "";
            result = GetValue(row, address);
            if (result == address)
                return result;
            result = FindChild(row, result);
            return result;

        }

        public static string GetValue(IRow row, string address)
        {
            double tem = 0;
            bool isNumber = double.TryParse(address, out tem);
            //数字 
            if (isNumber)
            {
                return tem < 0 ? $"({address})" : address;
            }
          

            string result = "";
            var cells = row.Cells;
            var cell = cells.FindFirst(t => t.Address.ToString() == address);
            if (cell != null)
            {
                result = cell.CellToString();
                result = FindChild(row, result);
            }
            else if (address.Contains("源") || address.Contains("模板"))
            {
                result = FindChild(row, address);
            }
            else
            {
                return address;
            }
            return result;


        }

        public static string FindChild(IRow row, string address)
        {

            //Excel公式预处理
            address = ExcelPreDeal(address);


            string result = "";
            if (string.IsNullOrEmpty(address))
                return "";
            double tem = 0;
            bool isNumber = double.TryParse(address, out tem);
            //数字 
            if (isNumber)
            {
                return tem < 0 ? $"({address})" : address;
            }


            string calStr = address;
            //分解公式
            char[] arr = ['+', '-', '*', '/', '(', ')'];
            //值
            var names = calStr.Split(arr);
            //运算符
            var op = calStr.Where(t => arr.Contains(t)).ToList();
            foreach (var name in names.Select((source, index) => new { source, index }))
            {
                string data = name.source;
                if (name.source.Contains("源") && name.source.Contains("!"))
                {
                    data = name.source.Replace("源", "").Replace("!", "").Replace("$", "");
                }
           

                if (op.Count > name.index)
                {
                    //乘除的运算优先级高
                    if (op[name.index] == '*' || op[name.index] == '/' || op[name.index] == '-')
                    {
                        if (!string.IsNullOrEmpty(data))
                        {
                            result += $"({GetValue(row, data)})" + op[name.index];
                        }
                        else
                        {
                            result += op[name.index];
                        }

                    }
                    else
                    {

                        result += GetValue(row, data) + op[name.index];
                    }


                }
                else
                {
                    result += GetRowValue(row, data);

                }
            }
            return result;
        }


        public static string ExcelPreDeal(string par)
        {
            if (string.IsNullOrEmpty(par))
                return "";

            string result = par;
         
            try
            {
                //按公式类型处理
                if (result.Contains("模板"))
                {

                    //VLOOKUP
                    if (result.Contains("VLOOKUP"))
                    {
                        //获取开始结束位置
                        int begin = result.IndexOf("VLOOKUP");
                        int end = result.IndexOf(")", begin);
                        //获取这段字符
                        string look = result.Substring(begin, end - begin + 1);
                        if (look.Contains("U15"))
                        {
                            result = result.Insert(begin, "U15");
                            result = result.Remove(begin + 3, end - begin + 3 - 2);
                        }
                        if (look.Contains("V13"))
                        {
                            result = result.Insert(begin, "V13");
                            result = result.Remove(begin + 3, end - begin + 3 - 2);
                        }
                    }

                    result = result.Replace("模板", "").Replace("!", "").Replace("$", "").Replace("&AW84&", "|str:/|");
                    //IF
                    if (result.Contains("IF"))
                    {
                        result = ChangeIF(result,2);
                    }
                  
                  



                    formulas.ForEach(t =>
                    {
                        if (t.ExcelName != null )
                        {
                            if(result.Contains(t.ExcelName))
                            {
                                result = result.ReplaceNumber(t.ExcelName, t.ReflectionProperty);
                            }
                            //针对3行进行转换  (U)V10+2=U12 
                            if (result.Contains("V"))
                            {
                                //DZ替换
                                if(t.ExcelName=="V13")
                                {
                                    if (result.Contains("V13"))
                                        result = result.Replace("V13", t.ReflectionProperty);
                                }
                                else
                                {
                                    //普通基础参数替换
                                    var eName = "V" + (Convert.ToInt32(t.ExcelName.Replace("U", "")) - 2);
                                    if (eName != "V13"&& result.Contains(eName))
                                        result = result.Replace(eName, t.ReflectionProperty);
                                }


                             
                            }

                        }
                    });


                }
              
                else
                {

                }
            }
            catch (Exception ex)
            {


            }

            
            
            return result;
        }


      

        /// <summary>
        /// 计算重量
        /// </summary>
        /// <param name="type"></param>
        /// <param name="number"></param>
        /// <returns></returns>
        public static decimal? CalWeight(string type)
        {
            if (string.IsNullOrEmpty(type))
                return 0;
            var size = type.Split('*');
            if (size != null && size.Count() > 0)
            {
                return Convert.ToDecimal(size[0]) * Convert.ToDecimal(size[1]) * Convert.ToDecimal(size[2])
                    / 1000000 * 7.85m;
            }
            else
            {
                return 0;
            }

        }
        /// <summary>
        /// 通过全称 获取国标ID
        /// </summary>
        /// <param name="par"></param>
        /// <returns></returns>
        public static int FindNSID(string par)
        {
            var ns = db.NSMaterials.FirstOrDefault(t => (t.Name + t.Type == par)
              || (par.Contains(t.Type) && par.Contains("H") && t.Name.Contains("H")));
            if (ns != null)
            {
                return ns.ID;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// IF判断语句切换
        /// </summary>
        /// <param name="par"></param>
        /// <param name="type">2  三行</param>
        /// <returns></returns>
        public static string ChangeIF(string par, int type)
        {

            //(IF(AND(V26<250),"错误",V26))   {if《U28<250》?错误:U28}
            if (type == 2)
            {
                //获取AND的位置
                int and_index = par.IndexOf("AND");
                //获取运算符前后的值
                string a, b, cal;
                var split_str = par.Substring(and_index + 1 + 3, par.IndexOf(",") - (and_index + 1 + 3 + 1));
                SplitCal(split_str, out a, out b, out cal);
                //获取结果的两个值
                int aa = par.IndexOf(",");
                int bb = par.IndexOf(")", aa);
                var split_str2 = par.Substring(par.IndexOf(",") + 1, par.IndexOf(")", par.IndexOf(",")) - (par.IndexOf(",") + 1)).Replace("\"", "");
                var split_list = split_str2.Split(",");
                var result = $"{{if《{a}{cal}{b}》?{split_list[0]}:{split_list[1]}}}";

                return result;

            }
            return par;
        }

        private static void SplitCal(string par, out string a, out string b, out string cal)
        {
            //对比 运算符
            List<string> calMarkA = new List<string>() { "<=", ">=", "!=" };
            List<string> calMarkB = new List<string>() { "<", "=", ">", };

            string result_a = "";
            string result_b = "";
            string result_cal = "";
            calMarkA.ForEach(calMark =>
            {
                if (par.Contains(calMark))
                {
                    var list = par.Split(calMark);
                    result_a = list[0];
                    result_b = list[1];
                    result_cal = calMark;
                }

            });
            if (string.IsNullOrEmpty(result_a))
            {
                calMarkB.ForEach(calMark =>
                {
                    if (par.Contains(calMark))
                    {
                        var list = par.Split(calMark);
                        result_a = list[0];
                        result_b = list[1];
                        result_cal = calMark;
                    }

                });

            }
            a = result_a;
            b = result_b;
            cal = result_cal;
        }

    }


    public class XiaoAo2Row
    {
        public string Title { get; set; }
        public List<KeyValue<string>> KeyValues { get; set; }
    }

    public class ExcelInfo
    {
        public string Type { get; set; }

        public string Contract { get; set; }

        public List<XiaoAo2Row> XiaoAo2Row { get; set; }


    }
}
